
<!DOCTYPE html>
<html lang="en" class="loading">
<head><meta name="generator" content="Hexo 3.9.0">
    <meta charset="UTF-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge,chrome=1">
    <meta name="viewport" content="width=device-width, minimum-scale=1.0, maximum-scale=1.0, user-scalable=no">
    <title>实用Mysql时间sql - kyralo</title>
    <meta name="apple-mobile-web-app-capable" content="yes">
    <meta name="apple-mobile-web-app-status-bar-style" content="black-translucent">
    <meta name="google" content="notranslate">
    <meta name="keywords" content="kyralo,"> 
    <meta name="description" content="MySql语句:今天select * from 表名 where to_days(时间字段名) = to_days(now());
昨天SELECT * FROM 表名 WHERE TO_DAYS(,"> 
    <meta name="author" content="kyralo"> 
    
    <link rel="icon" href="https://cdn.jsdelivr.net/gh/kyralo/cdn/ico/favicon.ico"> 
    <link rel="stylesheet" href="//cdn.jsdelivr.net/npm/gitalk@1/dist/gitalk.css">
    <link rel="stylesheet" href="https://cdn.jsdelivr.net/gh/kyralo/cdn@v1.0.2/css/diaspora.css">
    <!-- <script async src="//pagead2.googlesyndication.com/pagead/js/adsbygoogle.js"></script> -->
    <script>
         (adsbygoogle = window.adsbygoogle || []).push({
              google_ad_client: "ca-pub-8691406134231910",
              enable_page_level_ads: true
         });
    </script>
    <!-- <script async custom-element="amp-auto-ads"
        src="https://cdn.ampproject.org/v0/amp-auto-ads-0.1.js">
    </script> -->
</head>
</html>
<body class="loading">
    <span id="config-title" style="display:none">kyralo</span>
    <div id="loader"></div>
    <div id="single">
    <div id="top" style="display: block;">
    <div class="bar" style="width: 0;"></div>
    <a class="icon-home image-icon" href="javascript:;" data-url="https://kyralo.github.io"></a>
    <!-- <div title="播放/暂停" class="icon-play"></div> -->
    <h3 class="subtitle">实用Mysql时间sql</h3>
    <div class="social">
        <div>
            <div class="share">
                <a title="获取二维码" class="icon-scan" href="javascript:;"></a>
            </div>
            <div id="qr"></div>
        </div>
    </div>
    <div class="scrollbar"></div>
</div>

    <div class="section">
        <div class="article">
    <div class='main'>
        <h1 class="title">实用Mysql时间sql</h1>
        <div class="stuff">
            <span>八月 12, 2019</span>
            
  <ul class="post-tags-list"><li class="post-tags-list-item"><a class="post-tags-list-link" href="/tags/Mysql/">Mysql</a></li></ul>


        </div>
        <div class="content markdown">
            <h3 id="MySql语句"><a href="#MySql语句" class="headerlink" title="MySql语句:"></a>MySql语句:</h3><h4 id="今天"><a href="#今天" class="headerlink" title="今天"></a>今天</h4><p>select * from 表名 where to_days(时间字段名) = to_days(now());</p>
<h4 id="昨天"><a href="#昨天" class="headerlink" title="昨天"></a>昨天</h4><p>SELECT * FROM 表名 WHERE TO_DAYS( NOW( ) ) - TO_DAYS( 时间字段名) &lt;= 1</p>
<h4 id="7天"><a href="#7天" class="headerlink" title="7天"></a>7天</h4><p>SELECT * FROM 表名 where DATE_SUB(CURDATE(), INTERVAL 7 DAY) &lt;= date(时间字段名)</p>
<h4 id="近30天"><a href="#近30天" class="headerlink" title="近30天"></a>近30天</h4><p>SELECT * FROM 表名 where DATE_SUB(CURDATE(), INTERVAL 30 DAY) &lt;= date(时间字段名)</p>
<h4 id="本月"><a href="#本月" class="headerlink" title="本月"></a>本月</h4><p>SELECT * FROM 表名 WHERE DATE_FORMAT( 时间字段名, ‘%Y%m’ ) = DATE_FORMAT( CURDATE( ) , ‘%Y%m’ )</p>
<h4 id="上一月"><a href="#上一月" class="headerlink" title="上一月"></a>上一月</h4><p>SELECT * FROM 表名 WHERE PERIOD_DIFF( date_format( now( ) , ‘%Y%m’ ) , date_format( 时间字段名, ‘%Y%m’ ) ) =1</p>
<h4 id="查询本季度数据"><a href="#查询本季度数据" class="headerlink" title="查询本季度数据"></a>查询本季度数据</h4><p>select * from <code>ht_invoice_information</code> where QUARTER(create_date)=QUARTER(now());</p>
<h4 id="查询上季度数据"><a href="#查询上季度数据" class="headerlink" title="查询上季度数据"></a>查询上季度数据</h4><p>select * from <code>ht_invoice_information</code> where QUARTER(create_date)=QUARTER(DATE_SUB(now(),interval 1 QUARTER));</p>
<h4 id="查询本年数据"><a href="#查询本年数据" class="headerlink" title="查询本年数据"></a>查询本年数据</h4><p>select * from <code>ht_invoice_information</code> where YEAR(create_date)=YEAR(NOW());</p>
<h4 id="查询上年数据"><a href="#查询上年数据" class="headerlink" title="查询上年数据"></a>查询上年数据</h4><p>select * from <code>ht_invoice_information</code> where year(create_date)=year(date_sub(now(),interval 1 year));</p>
<h4 id="查询当前这周的数据"><a href="#查询当前这周的数据" class="headerlink" title="查询当前这周的数据"></a>查询当前这周的数据</h4><p>SELECT name,submittime FROM enterprise WHERE YEARWEEK(date_format(submittime,’%Y-%m-%d’)) = YEARWEEK(now());</p>
<h4 id="查询上周的数据"><a href="#查询上周的数据" class="headerlink" title="查询上周的数据"></a>查询上周的数据</h4><p>SELECT name,submittime FROM enterprise WHERE YEARWEEK(date_format(submittime,’%Y-%m-%d’)) = YEARWEEK(now())-1;</p>
<h4 id="查询当前月份的数据"><a href="#查询当前月份的数据" class="headerlink" title="查询当前月份的数据"></a>查询当前月份的数据</h4><p>select name,submittime from enterprise   where date_format(submittime,’%Y-%m’)=date_format(now(),’%Y-%m’)</p>
<h4 id="查询距离当前现在6个月的数据"><a href="#查询距离当前现在6个月的数据" class="headerlink" title="查询距离当前现在6个月的数据"></a>查询距离当前现在6个月的数据</h4><p>select name,submittime from enterprise where submittime between date_sub(now(),interval 6 month) and now();</p>
<h4 id="查询上个月的数据"><a href="#查询上个月的数据" class="headerlink" title="查询上个月的数据"></a>查询上个月的数据</h4><p>select name,submittime from enterprise   where date_format(submittime,’%Y-%m’)=date_format(DATE_SUB(curdate(), INTERVAL 1 MONTH),’%Y-%m’)</p>
<p>select * from <code>user</code> where DATE_FORMAT(pudate, ‘ %Y%m ‘ ) = DATE_FORMAT(CURDATE(), ‘ %Y%m ‘ ) ;</p>
<p>select * from user where WEEKOFYEAR(FROM_UNIXTIME(pudate,’%y-%m-%d’)) = WEEKOFYEAR(now())</p>
<p>select *<br>from user<br>where MONTH (FROM_UNIXTIME(pudate, ‘ %y-%m-%d ‘ )) = MONTH (now())</p>
<p>select *<br>from [ user ]<br>where YEAR (FROM_UNIXTIME(pudate, ‘ %y-%m-%d ‘ )) = YEAR (now())<br>and MONTH (FROM_UNIXTIME(pudate, ‘ %y-%m-%d ‘ )) = MONTH (now())</p>
<p>select *<br>from [ user ]<br>where pudate between 上月最后一天<br>and 下月第一天</p>
<p>where   date(regdate)   =   curdate();</p>
<p>select   *   from   test   where   year(regdate)=year(now())   and   month(regdate)=month(now())   and   day(regdate)=day(now())</p>
<p>SELECT date( c_instime ) ,curdate( )<br>FROM <code>t_score</code><br>WHERE 1<br>LIMIT 0 , 30</p>
<h3 id="Mybatis-xml文件中大于小于等于"><a href="#Mybatis-xml文件中大于小于等于" class="headerlink" title="Mybatis.xml文件中大于小于等于"></a>Mybatis.xml文件中大于小于等于</h3><h4 id="第一种写法："><a href="#第一种写法：" class="headerlink" title="第一种写法："></a>第一种写法：</h4><p>原符号 &lt; &lt;= &gt; &gt;= &amp; ‘ “<br>替换符号 &lt; &lt;= &gt; &gt;= &amp; &apos; &quot;<br>例如：sql如下：<br>create_date_time &gt;= #{startTime} and create_date_time &lt;= #{endTime}</p>
<h4 id="第二种写法："><a href="#第二种写法：" class="headerlink" title="第二种写法："></a>第二种写法：</h4><p>大于等于<br><!--[CDATA[ >= ]]--><br>小于等于<br><!--[CDATA[ <= ]]--><br>例如：sql如下：<br>create_date_time <!--[CDATA[ >= ]]--> #{startTime} and create_date_time <!--[CDATA[ <= ]]--> #{endTime}</p>

        </div>
        
    <div id='gitalk-container' class="comment link"
        data-ae='false'
        data-ci='eeec43124e53633945c0'
        data-cs='e2332cffb30ae24f185c1701286958cddd748ffb'
        data-r='kyralo.github.io'
        data-o='kyralo'
        data-a='kyralo'
        data-d='false'
    >查看评论</div>


    </div>
    
        <div class='side'>
            <ol class="toc"><li class="toc-item toc-level-3"><a class="toc-link" href="#MySql语句"><span class="toc-number">1.</span> <span class="toc-text">MySql语句:</span></a><ol class="toc-child"><li class="toc-item toc-level-4"><a class="toc-link" href="#今天"><span class="toc-number">1.1.</span> <span class="toc-text">今天</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#昨天"><span class="toc-number">1.2.</span> <span class="toc-text">昨天</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#7天"><span class="toc-number">1.3.</span> <span class="toc-text">7天</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#近30天"><span class="toc-number">1.4.</span> <span class="toc-text">近30天</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#本月"><span class="toc-number">1.5.</span> <span class="toc-text">本月</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#上一月"><span class="toc-number">1.6.</span> <span class="toc-text">上一月</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#查询本季度数据"><span class="toc-number">1.7.</span> <span class="toc-text">查询本季度数据</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#查询上季度数据"><span class="toc-number">1.8.</span> <span class="toc-text">查询上季度数据</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#查询本年数据"><span class="toc-number">1.9.</span> <span class="toc-text">查询本年数据</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#查询上年数据"><span class="toc-number">1.10.</span> <span class="toc-text">查询上年数据</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#查询当前这周的数据"><span class="toc-number">1.11.</span> <span class="toc-text">查询当前这周的数据</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#查询上周的数据"><span class="toc-number">1.12.</span> <span class="toc-text">查询上周的数据</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#查询当前月份的数据"><span class="toc-number">1.13.</span> <span class="toc-text">查询当前月份的数据</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#查询距离当前现在6个月的数据"><span class="toc-number">1.14.</span> <span class="toc-text">查询距离当前现在6个月的数据</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#查询上个月的数据"><span class="toc-number">1.15.</span> <span class="toc-text">查询上个月的数据</span></a></li></ol></li><li class="toc-item toc-level-3"><a class="toc-link" href="#Mybatis-xml文件中大于小于等于"><span class="toc-number">2.</span> <span class="toc-text">Mybatis.xml文件中大于小于等于</span></a><ol class="toc-child"><li class="toc-item toc-level-4"><a class="toc-link" href="#第一种写法："><span class="toc-number">2.1.</span> <span class="toc-text">第一种写法：</span></a></li><li class="toc-item toc-level-4"><a class="toc-link" href="#第二种写法："><span class="toc-number">2.2.</span> <span class="toc-text">第二种写法：</span></a></li></ol></li></ol>
        </div>
    
</div>


    </div>
</div>
</body>
<script src="//cdn.jsdelivr.net/npm/gitalk@1/dist/gitalk.min.js"></script>
<script src="//lib.baomitu.com/jquery/1.8.3/jquery.min.js"></script>
<script src="//cdn.jsdelivr.net/gh/kyralo/cdn/js/plugin.js"></script>
<script src="//cdn.jsdelivr.net/gh/kyralo/cdn/js/diaspora.js"></script>
<link rel="stylesheet" href="//cdn.jsdelivr.net/gh/kyralo/cdn/css/photoswipe.css">
<link rel="stylesheet" href="//cdn.jsdelivr.net/gh/kyralo/cdn/css/default-skin.css">
<script src="//cdn.jsdelivr.net/gh/kyralo/cdn/js/photoswipe.min.js"></script>
<script src="//cdn.jsdelivr.net/gh/kyralo/cdn/js/photoswipe-ui-default.min.js"></script>

<!-- Root element of PhotoSwipe. Must have class pswp. -->
<div class="pswp" tabindex="-1" role="dialog" aria-hidden="true">
    <!-- Background of PhotoSwipe. 
         It's a separate element as animating opacity is faster than rgba(). -->
    <div class="pswp__bg"></div>
    <!-- Slides wrapper with overflow:hidden. -->
    <div class="pswp__scroll-wrap">
        <!-- Container that holds slides. 
            PhotoSwipe keeps only 3 of them in the DOM to save memory.
            Don't modify these 3 pswp__item elements, data is added later on. -->
        <div class="pswp__container">
            <div class="pswp__item"></div>
            <div class="pswp__item"></div>
            <div class="pswp__item"></div>
        </div>
        <!-- Default (PhotoSwipeUI_Default) interface on top of sliding area. Can be changed. -->
        <div class="pswp__ui pswp__ui--hidden">
            <div class="pswp__top-bar">
                <!--  Controls are self-explanatory. Order can be changed. -->
                <div class="pswp__counter"></div>
                <button class="pswp__button pswp__button--close" title="Close (Esc)"></button>
                <button class="pswp__button pswp__button--share" title="Share"></button>
                <button class="pswp__button pswp__button--fs" title="Toggle fullscreen"></button>
                <button class="pswp__button pswp__button--zoom" title="Zoom in/out"></button>
                <!-- Preloader demo http://codepen.io/dimsemenov/pen/yyBWoR -->
                <!-- element will get class pswp__preloader--active when preloader is running -->
                <div class="pswp__preloader">
                    <div class="pswp__preloader__icn">
                      <div class="pswp__preloader__cut">
                        <div class="pswp__preloader__donut"></div>
                      </div>
                    </div>
                </div>
            </div>
            <div class="pswp__share-modal pswp__share-modal--hidden pswp__single-tap">
                <div class="pswp__share-tooltip"></div> 
            </div>
            <button class="pswp__button pswp__button--arrow--left" title="Previous (arrow left)">
            </button>
            <button class="pswp__button pswp__button--arrow--right" title="Next (arrow right)">
            </button>
            <div class="pswp__caption">
                <div class="pswp__caption__center"></div>
            </div>
        </div>
    </div>
</div>



<!-- Google Analytics -->
<script type="text/javascript">
  (function(i,s,o,g,r,a,m){i['GoogleAnalyticsObject']=r;i[r]=i[r]||function(){
  (i[r].q=i[r].q||[]).push(arguments)},i[r].l=1*new Date();a=s.createElement(o),
  m=s.getElementsByTagName(o)[0];a.async=1;a.src=g;m.parentNode.insertBefore(a,m)
  })(window,document,'script','https://www.google-analytics.com/analytics.js','ga');

  ga('create', 'UA-146789315-1', 'auto');
  ga('send', 'pageview');
</script>
<!-- End Google Analytics -->


<link rel="stylesheet" href="https://cdn.jsdelivr.net/npm/aplayer@1.10/dist/APlayer.min.css">
<script src="https://cdn.jsdelivr.net/npm/aplayer@1.10/dist/APlayer.min.js"></script>
<style>
  .aplayer .aplayer-lrc {
    height: 35px;
  }
  .aplayer .aplayer-lrc p{
    font-size: 16px;
    font-weight: 700;
    line-height: 18px !important;
  }
  .aplayer .aplayer-lrc p.aplayer-lrc-current{
    color: #FF1493;
  }
  .aplayer.aplayer-narrow .aplayer-body{
    left: -66px !important;
  }
  .aplayer.aplayer-fixed .aplayer-lrc {
    display: none;
  }
  .aplayer .aplayer-lrc.aplayer-lrc-hide {
      display:none !important;
  }
  .aplayer.aplayer-fixed .lrc-show {
    display: block;
    background: rgba(255, 255, 255, 0.8);
  }
</style>
<div class="aplayer"

    data-id="2725346449"

    data-server="netease"

    data-type="playlist"

    data-fixed="true"

    data-autoplay="false"

    data-loop="all"

    data-order="random"

    data-preload="auto"

    data-volume="0.7"

    data-mutex="true"

</div>
<script src="https://cdn.jsdelivr.net/npm/meting@1.2/dist/Meting.min.js"></script>
<script>
  $(function(){
    $('body').on('click', '.aplayer', function(){
      if($('.aplayer-button').hasClass('aplayer-play')) {
        $('.aplayer-lrc').removeClass('lrc-show');
      } else {
        $('.aplayer-lrc').addClass('lrc-show');
      }
    })
  });
</script>
</html>
